﻿//======================================================================
//
//        Copyright (C) 苏州逸仙软件    
//        All rights reserved
//
//        filename : D_T_ExperInfot_Exp.cs
//        description :
//
//        created by 周济 at 2010/12/21 14:30:19
//        mail:13912699233@139.com
//
//======================================================================


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace huaguwang
{
    public class D_T_ExperInfot_Exp : D_T_ExperInfot
    {
        public int Find(string name)
        {
            var strSql = ExpandMSSQL.GetPageSQL(
                    "dbo.T_ExperInfot",
                    " flag=1 and interiorkey like '%'+@name+'%'"
                    );
            var intRef = MSSQLHelper.SQLConType.C1.Exp_GetSingle(strSql,
                    new System.Data.SqlClient.SqlParameter("@name", SqlDbType.NVarChar)
                    {
                        Value = name
                    });
            return intRef.Exp_IntTryParse();
        }
        public DataTable Find(int begin, int page, string name)
        {
            var strSql = ExpandMSSQL.GetPageSQL(
                    begin,
                    page,
                    "a.id,a.title,a.interiorkey,uploaddate,UserID",
                    "dbo.T_ExperInfot a",
                    " a.id ",
                    " a.flag=1 and a.interiorkey like '%'+@name+'%'",
                    "");

            var str1 = new StringBuilder();
            str1.Append(" select id,uploaddate,username=dbo.f_getusername(userid),");
            str1.Append("title,");
            str1.Append("interiorkey,");
            str1.Append("bookcount=(");
            str1.Append("select count(a1.id) from t_ebook a1 where flag=1 and ExperInfotID=ab.id");
            str1.Append("),");
            str1.Append("voidecount=(");
            str1.Append("select count(a2.id) from t_video a2 where flag=1 and ExperInfotID=ab.id");
            str1.Append("),");
            str1.Append("formulacount=(");
            str1.Append("select count(a3.id) from t_formula a3 where flag=1 and ExperInfotID=ab.id");
            str1.Append("),");
            str1.Append("bolgcount=(");
            str1.Append("select count(a4.id) from t_expertblog a4 where flag=1 and expertid=ab.id");
            str1.Append(")");
            str1.Append(" from   (");
            str1.Append(strSql);
            str1.Append(" ) ab order by id desc ");


            var list = MSSQLHelper.SQLConType.C1.Exp_Query(
                    str1.ToString(),
                    new System.Data.SqlClient.SqlParameter("@name", SqlDbType.NVarChar)
                        {
                            Value = name
                        });
            return list.Tables[0];
        }


        //        select 
        //id,
        //title,
        //interiorkey,
        //bookcount=(
        //    select count(id) from t_ebook where flag=1 and ExperInfotID=a.id
        //),
        //voidecount=(
        //    select count(id) from t_video where flag=1 and ExperInfotID=a.id
        //),
        //formulacount=(
        //    select count(id) from t_formula where flag=1 and ExperInfotID=a.id
        //),
        //bolgcount=(
        //    select count(id) from t_expertblog where flag=1 and expertid=a.id
        //)
        //from dbo.T_ExperInfot  a

        /// <summary>
        /// 获得专家列表
        /// </summary>
        /// <returns></returns>
        public List<M_T_ExperInfot_Exp.NameAndID> DropDownListDatData()
        {
            string strsql = " select id,interiorkey from T_ExperInfot where flag=1 order by interiorkey";
            var list = MSSQLHelper.SQLConType.C1.Exp_GetList<M_T_ExperInfot_Exp.NameAndID>(
                    strsql,
                    dr => new M_T_ExperInfot_Exp.NameAndID()
                    {
                        ID = dr["id"].Exp_Trim(),
                        Name = dr["interiorkey"].Exp_Trim()
                    });
            return list;
        }
        /// <summary>
        /// 获得专家列表
        /// </summary>
        /// <returns></returns>
        public List<M_T_ExperInfot_Exp.NameAndID> DropDownListDatData(int id)
        {
            string strsql = " select id,interiorkey from T_ExperInfot where flag=1 and id=" + id + " order by interiorkey";
            var list = MSSQLHelper.SQLConType.C1.Exp_GetList<M_T_ExperInfot_Exp.NameAndID>(
                    strsql,
                    dr => new M_T_ExperInfot_Exp.NameAndID()
                    {
                        ID = dr["id"].Exp_Trim(),
                        Name = dr["interiorkey"].Exp_Trim()
                    });
            return list;
        }


    }
}
